from db import *
from util import *
from doc_util import *
import json
from config import *

d={"name_":"用户姓名","phone_":"手机号","login":"18100000000","company_name_":"公司名称","id_":"商务经理用户id","register_state":"已通过","login_":"登录名","company_name":"广东深华消防广州分公司","name":"王涛","register_state_":"注册状态(待认证,待提交,审核中,已通过,被退回,已失效)","id":1,"phone":"18100000000"}

print(d2j(d,4))

doc_list=[
    ['登入','基础','POST','/login',
        {"password_":"密码","login_":"登录名","login":"18100000000","password":"18300023454"},
        {
            "id":1,"id_":"商务经理用户id",
            "login":"18100000000","login_":"登录名",
            "name":"王涛","name_":"用户姓名",
            "company_name":"广东深华消防广州分公司","company_name_":"公司名称",
            "register_state":"已通过","register_state_":"注册状态(待认证,待提交,审核中,已通过,被退回,已失效)",
            "is_have_new_msg":1,"is_have_new_msg_":"是否有新消息",

            "phone":"18100000000","phone_":"手机号",
            "balance":"10233454.32","balance_":'商务经理可用余额',
            "invoice_amount":"3434223.23","invoice_count_":'商务经理办理完毕的开票数',
            "return_amount":"2342354.23","return_count_":'商务经理办理完毕的回款数',
            "pay_amount":"23234.32","pay_count_":'商务经理办理完毕的付款数',
            "deduct_amount":"2342.89","deduct_count_":'商务经理办理完毕的扣款数',
            "building_count":100,"building_count_":"在建项目数量",
            "building_project_count":20,"building_project_count_":"在建工程项目数量",
            "building_maintenance_count":30,"building_maintenance_count_":"在建维保项目数量",
            "building_detection_count":30,"building_detection_count_":"在建检测项目数量",
            #"building_sale_count":40,"building_sale_count_":"在建销售项目数量",
            #"building_service_count":10,"building_service_count_":"在建服务项目数量",

            "highest_risk_count":10,"highest_risk_count_":"高危风险数量",
            "high_risk_count":10,"high_risk_count_":"高风险数量",
            "common_risk_count":10,"common_risk_count_":"一般风险数量",

        },''],
    ['查询项目合同','基础','GET','/query__contract',
        {
            "type":"工程","type_":"项目类型(工程,维保,销售,检测,服务)",
            "search":"武汉光谷","search_":"搜索关键字（模糊匹配甲方名称，项目名称）",
            "risk_type_list":["高危风险","高风险"],"risk_type_list_":"包含风险列表",
            "is_balance_desc_order":0,"is_balance_desc_order_":"是否按余额由高到低排序",
            "min_return_ratio":"12.13","min_return_ratio_":"最小回款率",
            "max_return_ratio":"93.95","max_return_ratio_":"最大回款率",
            "offset":10,"offset_":"第一条数据所处位置",
            "limit":10,"limit_":"返回数据条数",

        },
        {
            "total":100,"total_":"符合条件总数量",
            "count":3,"count_":"本次返回数量",
            "result":[
                {
                    "name":"武汉光谷软件消防工程项目","name_":"合同名称",
                    "type":"工程","type_":"合同类型",
                    "balance":"128.34","balance_":"余额",
                    "return_ratio":"20.34","return_ratio_":"回款率",
                    "material_ratio":"30.34","material_ratio_":"材料费已付率",
                    "labor_ratio":"40.34","labor_ratio_":"人工费已付率",
                    "latest_invoice_ts":"2018-01-18 12:00:00","latest_invoice_ts_":"最近一次开票时间戳",
                    "latest_invoice_amount":"200000.00","latest_invoice_amount_":"最近一次开票金额",
                    "risk_level":"高危","risk_level_":"风险标识(高危,高,一般)",
                    "risk_text":"超过两个月未开票","risk_text_":"风险说明_",

                },
                {
                    "name":"南京光谷软件消防工程项目","name_":"合同名称",
                    "type":"工程","type_":"合同类型",
                    "balance":"128.34","balance_":"余额",
                    "return_ratio":"30.34","return_ratio_":"回款率",
                    "material_ratio":"30.34","material_ratio_":"材料费已付率",
                    "labor_ratio":"54.34","labor_ratio_":"人工费已付率",
                    "latest_invoice_ts":"2018-01-18 12:00:00","latest_invoice_ts_":"最近一次开票时间戳",
                    "latest_invoice_amount":"200000.34","latest_invoice_amount_":"最近一次开票金额",
                    "risk_level":"高危","risk_level_":"风险标识(高危,高,一般)",
                },
                {
                    "name":"深圳光谷软件消防工程项目","name_":"合同名称",
                    "type":"工程","type_":"合同类型",
                    "balance":"128.34","balance_":"余额",
                    "return_ratio":"30.34","return_ratio_":"回款率",
                    "material_ratio":"30.34","material_ratio_":"材料费已付率",
                    "labor_ratio":"54.34","labor_ratio_":"人工费已付率",
                    "latest_invoice_ts":"2018-01-18 12:00:00","latest_invoice_ts_":"最近一次开票时间戳",
                    "latest_invoice_amount":"200000.34","latest_invoice_amount_":"最近一次开票金额",
                    "risk_level":"高危","risk_level_":"风险标识(高危,高,一般)",
                },

            ],
            "result_":"结果数据",

        },

        ''],
    ['款项统计','基础','GET','/stat__account',
        {
            "begin_year_month":"2017-01","begin_year_month_":"开始年月",
            "end_year_month":"2017-06","end_year_month_":"结束年月",
            "offset":10,"offset_":"第一条数据所处位置",
            "limit":10,"limit_":"返回数据条数",
        },
        {
            "invoice_amount":"12033.23","invoice_amount_":"开票金额",
            "return_amount":"12033.23","return_amount_":"返回金额",
            "pay_amount":"-12033.23","pay_amount_":"付款金额",
            "deduct_amount":"-12033.23","deduct_amount_":"扣回金额",

            "total":100,"total_":"符合条件总数量",
            "count":3,"count_":"本次返回数量",
            "relate_contract_list":[
                {
                    "name":"武汉光谷软件消防工程项目","name_":"合同名称",
                    "type":"工程","type_":"合同类型",
                    "invoice_amount":"12033.23","invoice_amount_":"开票金额",
                    "return_amount":"12033.23","return_amount_":"返回金额",
                    "pay_amount":"-12033.23","pay_amount_":"付款金额",
                    "deduct_amount":"-12033.23","deduct_amount_":"扣回金额",

                },{
                    "name":"南京光谷软件消防工程项目","name_":"合同名称",
                    "type":"工程","type_":"合同类型",
                    "invoice_amount":"12033.23","invoice_amount_":"开票金额",
                    "return_amount":"12033.23","return_amount_":"返回金额",
                    "pay_amount":"-12033.23","pay_amount_":"付款金额",
                    "deduct_amount":"-12033.23","deduct_amount_":"扣回金额",

                },{
                    "name":"深圳光谷软件消防工程项目","name_":"合同名称",
                    "type":"工程","type_":"合同类型",
                    "invoice_amount":"12033.23","invoice_amount_":"开票金额",
                    "return_amount":"12033.23","return_amount_":"返回金额",
                    "pay_amount":"-12033.23","pay_amount_":"付款金额",
                    "deduct_amount":"-12033.23","deduct_amount_":"扣回金额",

                },

            ],
            "relate_contract_list_":"相关项目",

        },''],
    ['查询流水明细','基础','GET','/query__account_item',
        {
            "begin_year_month":"2017-01","begin_year_month_":"开始年月",
            "end_year_month":"2017-06","end_year_month_":"结束年月",
            "type_list":["开票","回款","付款","扣款"],"type_list_":"流水类型的多选筛选(开票,回款,付款,扣款)",
            "contract_id":10,"contract_id_":"项目合同id,可不传",
            "search":"武汉光谷","search_":"搜索关键字,按项目名称,甲方名称模糊匹配",
            "offset":10,"offset_":"第一条数据所处位置",
            "limit":10,"limit_":"返回数据条数",
        },
        {
            "total":100,"total_":"符合条件总数量",
            "count":3,"count_":"本次返回数量",
            "results":[
                {
                    "name":"武汉光谷软件消防工程项目","name_":"合同名称",
                    "code":"RPSQ1238349455","code_":"开票编号",
                    "create_ts":"2017-12-12 12:00:00","create_ts_":"流水创建时间戳",
                    "type":"开票","type_":"分类",
                    "cat":"材料费","cat_":"二级分类",

                    "remark":{
                        "invoice_receiver_name":"广东深华消防","invoice_receiver_name_":"收票单位",
                    },
                    "remark_":"备注字段",


                    "amount":"-12033.23","amount_":"金额",
                },{
                    "name":"武汉光谷软件消防工程项目","name_":"合同名称",
                    "code":"RPSQ1238349455","code_":"开票编号",
                    "create_ts":"2017-12-12 12:00:00","create_ts_":"流水创建时间戳",
                    "type":"回款","type_":"分类",
                    "cat":"材料费","cat_":"二级分类",
                    "remark":{
                        "payer_name":"广东深华消防","payer_name_":"付款方全称",
                        "payee_account":"中国银行艺园路支行23123934945","payee_account_":"收款帐号",
                    },
                    "amount":"-12033.23","amount_":"金额",
                },{
                    "name":"武汉光谷软件消防工程项目","name_":"合同名称",
                    "code":"RPSQ1238349455","code_":"开票编号",
                    "create_ts":"2017-12-12 12:00:00","create_ts_":"流水创建时间戳",
                    "type":"付款","type_":"分类",
                    "cat":"材料费","cat_":"二级分类",
                    "remark":{
                        "payee_name":"广东深华消防","payee_name_":"收款人",
                        "payee_account":"中国银行艺园路支行23123934945","payee_account_":"收款人帐号",
                    },

                    "amount":"-12033.23","amount_":"金额",
                },{
                    "name":"武汉光谷软件消防工程项目","name_":"合同名称",
                    "code":"RPSQ1238349455","code_":"开票编号",
                    "create_ts":"2017-12-12 12:00:00","create_ts_":"流水创建时间戳",
                    "type":"扣款","type_":"分类",
                    "cat":"平台服务费","cat_":"二级分类",
                    "remark":{
                        "return_amount":"712343.23","return_amount_":"回款",
                        "deduct_ratio":"23.43","deduct_ratio_":"扣款比例",
                    },

                    "amount":"-12033.23","amount_":"金额",
                },{
                    "name":"武汉光谷软件消防工程项目","name_":"合同名称",
                    "code":"RPSQ1238349455","code_":"开票编号",
                    "create_ts":"2017-12-12 12:00:00","create_ts_":"流水创建时间戳",
                    "type":"扣款","type_":"分类",
                    "cat":"借款本金","cat_":"二级分类",
                    "remark":{
                        "description":"借款本金扣除的本金","description_":"说明",
                    },
                    "amount":"-12033.23","amount_":"金额",
                },



            ],
            "results_":"查询结果",

        },''],

    ['查询预结算额明细','基础','GET','/query__settled_amount_item',
        {
            "begin_year_month":"2017-01","begin_year_month_":"开始年月",
            "end_year_month":"2017-06","end_year_month_":"结束年月",
            "type_list":["开票","回款","付款","扣款"],"type_list_":"流水类型的多选筛选(开票,回款,付款,扣款)",
            "contract_id":10,"contract_id_":"项目合同id,可不传",
            "search":"武汉光谷","search_":"搜索关键字,按项目名称,甲方名称模糊匹配",
            "offset":10,"offset_":"第一条数据所处位置",
            "limit":10,"limit_":"返回数据条数",
        },
        {
            "total":100,"total_":"符合条件总数量",
            "count":3,"count_":"本次返回数量",
            "results":[
                {
                    "type":"合同详情","type_":"类别",
                    "create_ts":"2017-12-12 12:00:00","create_ts_":"流水创建时间戳",
                    "amount":"234235.34","amount_":"金额",
                    "contract_name":"武汉光谷软件消防工程项目","contract_name_":"合同名称",
                    "contract_code":"S4584353066","contract_code_":"合同编号",
                    "contract_party_a_name":"合同甲方名称","contract_party_a_name_":"合同编号",
                    "contract_begin_date":"2017-12-12","contract_begin_date_":"合同开始日期",
                    "contract_end_date":"2017-12-25","contract_end_date_":"合同结束日期",

                },{
                    "type":"补充合同详情","type_":"类别",
                    "create_ts":"2017-12-12 12:00:00","create_ts_":"流水创建时间戳",
                    "amount":"-12033.23","amount_":"金额",
                    "contract_name":"武汉光谷软件消防工程项目","contract_name_":"合同名称",
                    "contract_code":"S4584353066","contract_code_":"合同编号",
                    "contract_party_a_name":"合同甲方名称","contract_party_a_name_":"合同编号",
                    "contract_begin_date":"2017-12-12","contract_begin_date_":"合同开始日期",
                    "contract_end_date":"2017-12-25","contract_end_date_":"合同结束日期",
                },{
                    "type":"施工现场结算单","type_":"类别",
                    "create_ts":"2017-12-12 12:00:00","create_ts_":"流水创建时间戳",
                    "amount":"-12033.23","amount_":"金额",
                    "contract_name":"武汉光谷软件消防工程项目","contract_name_":"合同名称",
                    "contract_code":"S4584353066","contract_code_":"合同编号",
                    "contract_party_a_name":"合同甲方名称","contract_party_a_name_":"合同编号",
                    "contract_begin_date":"2017-12-12","contract_begin_date_":"合同开始日期",
                    "contract_end_date":"2017-12-25","contract_end_date_":"合同结束日期",
                }

            ],
            "results_":"查询结果",

        },''],

    ['统计流水数量金额','基础','GET','/stat__account_item__count_account',
        {
            "begin_year_month":"2017-01","begin_year_month_":"开始年月",
            "end_year_month":"2017-06","end_year_month_":"结束年月",
            "contract_id":10,"contract_id_":"项目合同id,可不传",

        },
        {
            "settled_amount":"1234.34","settled_amount_":'预结算总额',
            "invoice_count":1232,"invoice_count_":'办理完毕的开票数',
            "return_count":123,"return_count_":'办理完毕的回款数',
            "pay_count":12,"pay_count_":'办理完毕的付款数',
            "deduct_count":124,"deduct_count_":'办理完毕的扣款数',
            "invoice_amount":"34545.65","invoice_amount_":'办理完毕的开票金额',
            "return_amount":"345445.65","return_amount_":'办理完毕的回款金额',
            "pay_amount":"-345452.65","pay_amount_":'办理完毕的付款金额',
            "deduct_amount":"-34545.65","deduct_amount_":'办理完毕的扣款金额',


        },''],

]

doc_db_list=[doc[0:4]+[d2j(doc[4])]+[d2j(doc[5])]+[doc[6]] for doc in doc_list]
with DBConnection(DBPOOL) as conn:
    cr=conn.cursor()
    cr.execute("DELETE FROM t_doc")

    for doc in doc_list:
        response_example=copy.deepcopy(doc[5])
        row=doc[0:4]+[d2j(doc[4])]+[d2j(doc[5])]+[d2j(response_example)]+[doc[6]]
        dl=cr.list("SELECT * FROM t_doc WHERE name='%s'"%row[0])
        if dl.__len__()==0:
            clear_dict(response_example,'data')
            row[6]=d2j(response_example)
            cr.insert_many([row],'t_doc',['name','cat','method','url','request_json','response_json','response_example','remark'])
        else:
            cr.execute("UPDATE t_doc SET cat='%s',method='%s',url='%s',request_json='%s',response_json='%s',remark='%s' WHERE name='%s'"%tuple(row[1:6]+[row[7]]+[row[0]]))
            #cr.execute("UPDATE doc SET cat='%s',method='%s',url='%s',request_json='%s',response_json='%s',remark='%s' WHERE name='%s'"%row[1:6]+[row[7]]+[row[0]])
